﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using DTO;


namespace DA
{
    // phiếu đăng ký
    public class PhieuDangKyDA:DBConnection
    {
        // hàm tạo
        public PhieuDangKyDA()
        {
            sqlCommand.Connection = sqlConnection;
        }
        // lấy tất cả
        public DataSet GetAll()
        {
            var ds = new DataSet();
            const string query = "select * from PHIEUDANGKY";
            if (open())
            {
                sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
                sqlDataAdapter.Fill(ds);
                
            }
            close();
            return ds;
        }
        public int GetMaMax()
        {
            const string query = "select MAX(MaPhieuDangKy) from PhieuDangKy";
            var id = 0;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return 0;
                }
                while (sqlDataReader.Read())
                {
                    id = sqlDataReader.GetInt32(0);
                }
                close();
                return id;
            }
            catch
            {
                return 0;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
        }
        // thêm 
        public int Add(PhieuDangKyDTO item)
        {
            var query = String.Format("insert into PHIEUDANGKY values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}'); select SCOPE_IDENTITY();", 
                item.MaDiaOc, item.TuNgay, item.DenNgay, item.SoTien, 
                item.ThoiGianHenChupAnh, item.MaToBuom, item.MaQCBao, 
                item.MaQCBang, item.TinhTrangThanhToan);
            sqlCommand.CommandText = query;
            try
            {
                open();
                var idRe = -1;
                sqlCommand.CommandText = query;
                var re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return -1;
            }
        }
        // sửa
        public bool Update(PhieuDangKyDTO item)
        {
            var query = String.Format("Update PHIEUDANGKY set MaDiaOc='{0}',TuNgay=N'{1}',DenNgay='{2}',SoTien='{3}',ThoiGianHenChupAnh=N'{4}',MaToBuom=N'{5}',MaQCBao=N'{6}',MaQCBang=N'{7}',TinhTrangThanhToan='{8}' where MaPhieuDangKy='{9}'",
                item.MaDiaOc, item.TuNgay, item.DenNgay, item.SoTien, 
                item.ThoiGianHenChupAnh, item.MaToBuom, item.MaQCBao,
                item.MaQCBang, item.TinhTrangThanhToan, item.MaPhieuDangKy);
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        // xóa 
        public bool Delete(int ma_item)
        {
            var query = "Delete from PHIEUDANGKY where MaPhieuDangKy = " + ma_item;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }

        // TODO chưa test
        // gia hạn phiếu đk
        public bool giahanphieudangky(PhieuDangKyDTO item)
        {
            var query = String.Format("Update PHIEUDANGKY set DenNgay='{0}'", item.DenNgay);
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        public string GetInfo(string _Madiaoc, object _Tenthuoctinh)
        {
            string query = String.Format("Select {0} from PHIEUDANGKY where MaDiaOc='{1}'",_Tenthuoctinh,_Madiaoc );
            var values = string.Empty;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return string.Empty;
                }
                while (sqlDataReader.Read())
                {
                    values = sqlDataReader[0].ToString();
                }
                close();
                return values;
            }
            catch
            {
                return values;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
        }
        public string GetTinhTrangThanhToan(string maphieudangky)
        {
            MyQueryDA myqueryDA = new MyQueryDA();
            return myqueryDA.getValues(String.Format("select TinhTrangThanhToan from PHIEUDANGKY where MaPhieuDangKy='{0}'", maphieudangky));
        }
        public bool ThanhToan(string _MaPhieuDangKy)
        {
            MyQueryDA myqueryDA = new MyQueryDA();
            string query = String.Format("update PHIEUDANGKY set TinhTrangThanhToan='1' where MaPhieuDangKy='{0}'", _MaPhieuDangKy);
            return myqueryDA.doNonQuery(query);
        }
        public int GetMaPhieuDangKy(string _Madiaoc)
        {
            MyQueryDA myqueryDA = new MyQueryDA();
            string query = String.Format("select MaPhieuDangKy from PHIEUDANGKY where MaDiaOc='{0}'", _Madiaoc);
            return myqueryDA.getId(query);
        }
        public int getMaChiTietToBuom(string _madiaoc)
        {
            string queryMachitiettobuom = String.Format("Select MaChiTietToBuom from TOBUOM,PHIEUDANGKY where PHIEUDANGKY.MaToBuom=TOBUOM.MaToBuom and MaDiaOc='{0}'", _madiaoc);
            return new MyQueryDA().getId(queryMachitiettobuom);
        }
        public int getMaChiTietBang(string _Madiaoc)
        {
            string queryMachitietbang = String.Format("Select MaChiTietBang from QC_BANG,PHIEUDANGKY where PHIEUDANGKY.MaQCBang=QC_BANG.MaQCBANG and MaDiaOc='{0}'", _Madiaoc);
            return new MyQueryDA().getId(queryMachitietbang);
        }
        public int getMaChiTietBao(string _Madiaoc)
        {
            string queryMachitietbao = String.Format("Select MaChiTietBao from QCBAO,PHIEUDANGKY where PHIEUDANGKY.MaQCBao=QCBAO.MaQCBAO and MaDiaOc='{0}'",_Madiaoc );
            return new MyQueryDA().getId(queryMachitietbao);
        }
    }
}
